package com.centerserver;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DatabaseManager {

	private Connection mConnection;
	private Statement mStatement;
	private final static String mDataBaseName = "wvss";
	private final static String mUserName = "root";
	private final static String mPassword = "root";
	private final static String mUseUnicode = "true";
	private final static String mCharacterEncoding = "utf8";

	public DatabaseManager() {
		
	}

	public boolean connectDatabase() {

		try {
			Class.forName("org.gjt.mm.mysql.Driver").newInstance();
		} catch (InstantiationException e2) {

			e2.printStackTrace();
		} catch (IllegalAccessException e2) {
			e2.printStackTrace();
		} catch (ClassNotFoundException e2) {
			e2.printStackTrace();
		}

		String url = "jdbc:mysql://localhost/" + mDataBaseName + "?user="
				+ mUserName + "&password=" + mPassword + "&useUniCode="
				+ mUseUnicode + "&characterEncoding=" + mCharacterEncoding;

		try {
			mConnection = DriverManager.getConnection(url);
		} catch (SQLException e1) {
			System.out.println("SQLException in DatabaseManager");
			e1.printStackTrace();
		}

		try {
			mStatement = mConnection.createStatement();
		} catch (SQLException e) {
			System.out.println("SQLException in DatabaseManager");
			e.printStackTrace();
		}

		return true;
	}

	public boolean isCaptureEndAvailable(String captureEndName) {

		String sql = "select * from capture_end where capture_end_name = '"
				+ captureEndName + "'";

		boolean isAvailable = false;

		try {
			ResultSet resultSet = mStatement.executeQuery(sql);
			while (resultSet.next()) {
				isAvailable = resultSet.getBoolean("is_available");
			}

		} catch (SQLException e) {
			System.out.println("SQLException in DatabaseManager");
			e.printStackTrace();
		}
		return isAvailable;
	}

	public List<String> getAllAvailableCaptureEndName() {

		List<String> res = new ArrayList<String>();
		String sql = "select * from capture_end where is_available = 1";

		try {
			ResultSet resultSet = mStatement.executeQuery(sql);
			while (resultSet.next()) {
				res.add(resultSet.getString("capture_end_name"));
			}
		} catch (SQLException e) {
			System.out.println("SQLException in DatabaseManager");
			e.printStackTrace();
		}

		return res;
	}

	public List<String> getAllCaptureEndName() {

		List<String> res = new ArrayList<String>();
		String sql = "select * from capture_end";

		try {
			ResultSet resultSet = mStatement.executeQuery(sql);
			while (resultSet.next()) {
				res.add(resultSet.getString("capture_end_name"));
			}
		} catch (SQLException e) {
			System.out.println("SQLException in DatabaseManager");
			e.printStackTrace();
		}

		return res;
	}

	public boolean addCaptureEnd(String captureEndName,
			String captureEndIpAddress) {

		String sql = "select * from capture_end where capture_end_name = '"
				+ captureEndName + "'";

		try {
			ResultSet resultSet = mStatement.executeQuery(sql);

			// new capture end
			if (!resultSet.next()) {
				sql = "insert into capture_end(capture_end_name, ip_address, is_available) values('"
						+ captureEndName
						+ "', '"
						+ captureEndIpAddress
						+ "' ,1)";
				mStatement.execute(sql);
				return true;
			} else {
				sql = "update capture_end set is_available = 1";
				mStatement.execute(sql);
				return false;
			}
		} catch (SQLException e) {
			System.out.println("SQLException in DatabaseManager");
			e.printStackTrace();
		}
		return false;
	}

	public List<String> getAllCaptureVideo(String captureEndName) {

		List<String> res = new ArrayList<String>();
		String sql = "select * from capture_end where capture_end_name = '"
				+ captureEndName + "'";
		int captureEndId = 0;

		try {
			ResultSet resultSet = mStatement.executeQuery(sql);

			// get the ID of capture end by the capture end name
			if (resultSet.next()) {
				captureEndId = resultSet.getInt("capture_end_id");
			}

			sql = "select * from capture_video_item where capture_end_id = "
					+ captureEndId;
			resultSet = mStatement.executeQuery(sql);

			while (resultSet.next()) {
				res.add(resultSet.getString("video_name"));
			}

		} catch (SQLException e) {
			System.out.println("SQLException in DatabaseManager");
			e.printStackTrace();
		}

		return res;
	}

	public void disableAllCaptureEnd() {

		String sql = "update capture_end set is_available = 0 where is_available = 1";

		try {
			mStatement.execute(sql);
		} catch (SQLException e) {
			System.out.println("SQLException in DatabaseManager");
			e.printStackTrace();
		}
	}

	public boolean hasAvailableCaptureEnd() {
		String sql = "select * from capture_end where is_available = 1";

		ResultSet resultSet;
		try {
			resultSet = mStatement.executeQuery(sql);

			return resultSet.next();

		} catch (SQLException e) {
			System.out.println("SQLException in DatabaseManager");
			e.printStackTrace();
		}
		return false;
	}

	public String getCaptureEndName(String ipAddress) {

		String sql = "select * from capture_end where ip_address = '"
				+ ipAddress + "'";

		try {
			ResultSet resultSet = mStatement.executeQuery(sql);
			if (resultSet.next()) {
				return resultSet.getString("capture_end_name");
			}
		} catch (SQLException e) {
			System.out.println("SQLException in DatabaseManager");
			e.printStackTrace();
		}

		return null;
	}

	public String getIpAddressByCaptureEndName(String captureEndName) {

		String sql = "select * from capture_end where capture_end_name = '"
				+ captureEndName + "'";

		try {
			ResultSet resultSet = mStatement.executeQuery(sql);
			if (resultSet.next()) {
				return resultSet.getString("ip_address");
			}
		} catch (SQLException e) {
			System.out.println("SQLException in DatabaseManager");
			e.printStackTrace();
		}

		return null;

	}

	public void saveSps(String ipAddress, String sps) {

		String sql = "update capture_end set sps = '" + sps
				+ "' where ip_address = '" + ipAddress + "'";

		try {
			mStatement.execute(sql);
		} catch (SQLException e) {
			System.out.println("SQLException in DatabaseManager");
			e.printStackTrace();
		}
	}

	public void savePps(String ipAddress, String pps) {
		String sql = "update capture_end set pps = '" + pps
				+ "' where ip_address = '" + ipAddress + "'";

		try {
			mStatement.execute(sql);
		} catch (SQLException e) {
			System.out.println("SQLException in DatabaseManager");
			e.printStackTrace();
		}
	}
	
	public String getSpsByCaptureEndName(String captureEndName) {

		String sql = "select * from capture_end where capture_end_name = '"
				+ captureEndName + "'";

		try {
			ResultSet resultSet = mStatement.executeQuery(sql);
			if (resultSet.next()) {
				return resultSet.getString("sps");
			}
		} catch (SQLException e) {
			System.out.println("SQLException in DatabaseManager");
			e.printStackTrace();
		}

		return null;

	}
	
	public String getPpsByCaptureEndName(String captureEndName) {

		String sql = "select * from capture_end where capture_end_name = '"
				+ captureEndName + "'";

		try {
			ResultSet resultSet = mStatement.executeQuery(sql);
			if (resultSet.next()) {
				return resultSet.getString("pps");
			}
		} catch (SQLException e) {
			System.out.println("SQLException in DatabaseManager");
			e.printStackTrace();
		}

		return null;
	}
	
	public boolean addCaptureVideoItem(String ipAddress, String videoName) {

		String sql = "select * from capture_end where ip_address = '"
				+ ipAddress + "'";

		try {
			ResultSet resultSet = mStatement.executeQuery(sql);
			
			// new video item
			if (resultSet.next()) {
				sql = "insert into capture_video_item(capture_end_id, video_name) values("
						+ resultSet.getInt("capture_end_id")
						+ ", '"
						+ videoName
						+ "')";
				mStatement.execute(sql);
				return true;
			} else {
				System.out.println("no such ip address!!!");
				return false;
			}
		} catch (SQLException e) {
			System.out.println("SQLException in DatabaseManager");
			e.printStackTrace();
		}
		return false;
	}
}
